package dataBaseWrapper;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

/** Class used to connect to the Group T database. Contains functions to check if the SQL Driver is present,
 * and to insert and retrieve information from the tables used in the program.
 * 
 * @
 *
 */
public class DataBaseConnection {

	
	/** Deletes one hour from the calendar. 
	 * 
	 * @param properties A string that contains information to retrieve the table row that needs to be deleted, in format (UserID, EventID, Year, Month, Row, Column).
	 * @throws SQLException Thrown when deletion or creating/closing the connection fails.
	 */
	public static void deleteEvent(String[] properties) throws SQLException {
		Connection con = null;
		Statement stat = null;
		try {
			con = DriverManager.getConnection(
					"jdbc:sqlserver://studev.groept.be\\groept;databasename=a13_SD112","a13_SD112","a13_SD112");
			stat = con.createStatement();
			String query = "DELETE FROM [a13_SD112].[dbo].SunTime_EventDays WHERE UserID = '" + properties[0] + "' AND EventID = '" + properties[1] + "' AND Year  = '" + properties[2] + "' AND Month  = '" + properties[3] + "' AND Week  = '" + properties[4] + "' AND Row  = '" + properties[5] + "' AND ColumnPos = '" + properties[6] + "';";
			System.out.println(query);
			stat.execute(query);
		} catch (SQLException e) {
			System.out.println("cannot connect!");
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/** Retrieves information about the hours users are available from the database. The
	 * information is presented in the format (EventDayID, UserID, EventID, Year, Month, Week, Row and Column on the calendar.
	 * @return A CachedRowSet that contains all information about the available hours of the users.
	 * @throws SQLException Thrown when opening, closing or querying the database connection failed.
	 */
	public static CachedRowSet getEventData() throws SQLException {
		Connection con = null;
		Statement stat = null;
		ResultSet result = null; 
		CachedRowSet finalResult = new CachedRowSetImpl();
		try {

			con = DriverManager.getConnection(
					"jdbc:sqlserver://studev.groept.be\\groept;databasename=a13_SD112","a13_SD112","a13_SD112");
			stat = con.createStatement();
			result = stat.executeQuery("SELECT * from SunTime_EventDays");
			if(result.next())
			{
				finalResult.populate(result);
			}
		} catch (SQLException e) {
			System.out.println("cannot connect!");
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return finalResult;
	}
	
	/** Retrieves information about the users. This consists of ID and name.
	 * @return A CachedRowSet containing the users.
	 * @throws SQLException Thrown when the database connection cannot be opened, queried or closed.
	 */
	public static CachedRowSet getUsers() throws SQLException {
		Connection con = null;
		Statement stat = null;
		ResultSet result = null;
		CachedRowSet finalResult = new CachedRowSetImpl();
		try {
			con = DriverManager.getConnection(
					"jdbc:sqlserver://studev.groept.be\\groept;database = a13_SD112","a13_SD112","a13_SD112");
			stat = con.createStatement();
			result = stat.executeQuery("SELECT * from SunTime_Users;");
			if(result.next())
			{
				finalResult.populate(result);
			} //CachedRowSet contains the results from the database.
		} catch (SQLException e) {
			
			System.out.println("cannot connect!");
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return finalResult;
	}
	/** Retrieves information about the events. This consists of ID, name and location.
	 * @return A CachedRowSet containing the events.
	 * @throws SQLException Thrown when the database connection cannot be opened, queried or closed.
	 */
	public static CachedRowSet getEvents() throws SQLException {
		Connection con = null;
		Statement stat = null;
		ResultSet result = null; 
		CachedRowSet finalResult = new CachedRowSetImpl();
		try {
			con = DriverManager.getConnection(
					"jdbc:sqlserver://studev.groept.be\\groept;database = a13_SD112","a13_SD112","a13_SD112");
			stat = con.createStatement();
			result = stat.executeQuery("SELECT * from SunTime_Events;");
			if(result.next())
			{
				finalResult.populate(result);
			}
		} catch (SQLException e) {
			System.out.println("cannot connect!");
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return finalResult;
	}
	/** Checks if the driver required for using the database has been loaded.
	 * 
	 */
	public static void loadDriver() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			System.out.println("driver com.microsoft.sqlserver.jdbc.SQLServerDriver loaded!");
		} catch (ClassNotFoundException e) {
			System.out.println("driver com.microsoft.sqlserver.jdbc.SQLServerDriver not found!");
			e.printStackTrace();
		}
		
	}
	
/** Inserts information into the database.
 * @param properties The string of properties to be inserted into the database, see the getter functions for formatting.
 * @param databaseName The name of the database that has to be written to.
 *@throws SQLException Thrown when opening or closing the database connection fails, or the information could not be inserted.
 */
public static void insertData(String[] properties, String databaseName) {
		Connection con = null;
		Statement stat = null;
		String query = null;
		try {
			con = DriverManager.getConnection(
					"jdbc:sqlserver://studev.groept.be\\groept;databaseName=a13_SD112","a13_SD112","a13_SD112");
			switch (databaseName)
			{
				case "SunTime_Users": query = "INSERT INTO " + databaseName + "(Name) VALUES ("; break;
				case "SunTime_Events": query = "INSERT INTO " + databaseName + "(Name, Location) VALUES ("; break;
				case "SunTime_EventDays": query = "INSERT INTO " + databaseName + "(UserID,EventID,Year,Month,Week,Row,ColumnPos) VALUES ("; break;
			}
			
			for(int i = 0;i < properties.length;i++)
			{
				if(i < properties.length - 1)
				{
					query = query + "'" + properties[i] + "', ";
				}
				else
				{
					query = query + "'" + properties[i] + "');"; //Closes brackets on last property.
				}			
			}
			System.out.println(query);
			stat = con.createStatement();
			stat.executeUpdate(query);
		} catch (SQLException e) {
			System.out.println("cannot connect!");
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	
	
}
